import pymysql
import sqlite3
from DBUtils.PooledDB import PooledDB

class MySQL():
    host = "47.101.188.240"
    user = "root"
    port = 3306
    pasword = 'cherny1314.'
    charset = 'utf8'
    pool = None
    limit_count = 10  # 最低预启动数据库连接数量
    col = {}

    def __init__(self, tab):
        self.db = tab
        self.pool = PooledDB(pymysql, self.limit_count, host=self.host, user=self.user, passwd=self.pasword, db=self.db,
                             port=self.port, charset=self.charset, use_unicode=True)

    def get_index(self, tab):
        conn = self.pool.connection()
        cursor = conn.cursor()
        cursor.execute('select * from {} where id = "00"'.format(tab))
        cols = [i[0] for i in cursor.description]
        cursor.close()
        conn.close()
        return cols

    def add_col(self, tab, cols):
        if tab not in self.col:
            self.col[tab] = set(self.get_index(tab))
        s = set(cols)-self.col[tab]
        if s:
            sql = ["alter table {} add `{}` varchar(255)".format(tab, i) for i in s]
            print(sql)
            self.insert(sql)
            self.col[tab] = set(cols) & self.col[tab]
        # return s

    def select(self, sql):
        conn = self.pool.connection()
        cursor = conn.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
        return result

    def insert(self, sql):
        conn = self.pool.connection()
        cursor = conn.cursor()
        try:
            if isinstance(sql, list):
                [cursor.execute(i) for i in sql]
            else:
                cursor.execute(sql)
            conn.commit()
            return {'result': True, 'id': int(cursor.lastrowid)}
        except Exception as err:
            conn.rollback()
            return {'result': False, 'err': err, "sql": sql}
        finally:
            cursor.close()
            conn.close()
